//
//  ZLPDataBaseHandle.m
//  TangYi
//
//  Created by dllo on 15/11/2.
//  Copyright © 2015年 赵连鹏. All rights reserved.
//

#import "ZLPDataBaseHandle.h"
#import <sqlite3.h>

@implementation ZLPDataBaseHandle

{
    sqlite3 *dbPoint;
}

+ (instancetype)shareDataBaseHandle {
    static ZLPDataBaseHandle *handle;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
       
        handle = [[ZLPDataBaseHandle alloc] init];
        
    });
    return handle;
}

- (void)openDB {
    NSString *sandBoxPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *dbPath = [sandBoxPath stringByAppendingPathComponent:@"Topic.sqlite"];
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"打开成功");
    } else {
        NSLog(@"打开失败");
    }
    NSLog(@"%@", dbPath);
}

- (void)createTable {
    NSString *sqlStr = @"create table if not exists Topic (title text, pic text, likes text, type text, numID text, flag text)";
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建成功");
    } else {
        NSLog(@"创建失败");
    }
}

- (SelectTopic)isHaveInTable:(ZLPTableViewModel *)topic {
    NSString *sqlStr = [NSString stringWithFormat:@"select *from Topic where numID = '%@' and flag = '1'", topic.numID];
    // 跟随指针
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dbPoint, [sqlStr UTF8String], -1, &stmt, nil);
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        if (sqlite3_step(stmt) == SQLITE_ROW) {
            // 释放跟随指针
            sqlite3_finalize(stmt);
            return InTable;
        } else {
            sqlite3_finalize(stmt);
            return NotInTable;
        }
    } else {
        NSLog(@"查询失败");
        sqlite3_finalize(stmt);
        return SelectError;
    }
}

- (void)saveTopicInDB:(ZLPTableViewModel *)topic {
    NSString *sqlStr = [NSString stringWithFormat:@"insert into Topic(title, pic, likes, type, numID, flag) values ('%@', '%@', '%@', '%@', '%@', '1')", topic.title, topic.pic, topic.likes, topic.type, topic.numID];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"添加成功");
    } else {
        NSLog(@"添加失败");
    }
}

- (void)deleteTopicInDB:(ZLPTableViewModel *)topic {
    NSString *sqlStr = [NSString stringWithFormat:@"update Topic set flag = '0' where numID = '%@'", topic.numID];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
}

- (NSMutableArray *)selectAllTopic {
    NSMutableArray *topicArr = [NSMutableArray array];
    NSString *sqlStr = @"select * from Topic where flag = '1'";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dbPoint, [sqlStr UTF8String], -1, &stmt, nil);
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            ZLPTableViewModel *topic = [[ZLPTableViewModel alloc] init];
            topic.title = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
            topic.pic = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            topic.likes = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            topic.type = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];
            topic.numID = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 4)];
            [topicArr addObject:topic];
            [topic release];
        }
    }
    
    sqlite3_finalize(stmt);
    
    return topicArr;
}


@end
